package com.lyouu.dataminer.bustour.datatransfer;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;

import org.apache.log4j.Logger;
import org.joda.time.DateTime;
import org.joda.time.Days;

import com.lyouu.dataminer.db.DbConstants;
import com.lyouu.dataminer.factory.MysqlDbFactory;
import com.lyouu.dataminer.util.Util;

public class BustourScheduleTransfer {
	private Logger logger = Logger.getLogger(BustourScheduleTransfer.class);

	public BustourScheduleTransfer() {

	}

	public void changeDateType() throws SQLException {
		Connection conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
		Statement stmt = conn.createStatement();

		String sql = "DELETE FROM ly_bustour_schedule";
		stmt.executeUpdate(sql);

		sql = "SELECT * FROM ly_bustour_schedule_bak";
		ResultSet rs = stmt.executeQuery(sql);

		sql = "INSERT INTO bustour_schedule (sid,provider,uid,tourname,groupid,departdate,enddate,duration,quota,signed,price,preprice,childprice,5groupprice,priceunit,status,booking,pageurl) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement prestmt = conn.prepareStatement(sql);
		int departMonth, departDay, endMonth, endDay;
		while (rs.next()) {
			prestmt.setInt(1, rs.getInt(1));
			prestmt.setString(2, rs.getString(2));
			prestmt.setInt(3, rs.getInt(3));
			prestmt.setString(4, rs.getString(4));
			prestmt.setString(5, rs.getString(5));
			departMonth = Integer.valueOf(rs.getString(6).split("\\.")[0]) - 1;
			departDay = Integer.valueOf(rs.getString(6).split("\\.")[1]);
			endMonth = Integer.valueOf(rs.getString(7).split("\\.")[0]) - 1;
			endDay = Integer.valueOf(rs.getString(7).split("\\.")[1]);
			if (departMonth > 5) {
				prestmt.setDate(6, new Date(114, departMonth, departDay));
			} else {
				prestmt.setDate(6, new Date(115, departMonth, departDay));
			}
			if (endMonth > 5) {
				prestmt.setDate(7, new Date(114, endMonth, endDay));
			} else {
				prestmt.setDate(7, new Date(115, endMonth, endDay));
			}
			prestmt.setInt(8, rs.getInt(8));
			prestmt.setInt(9, rs.getInt(9));
			prestmt.setInt(10, rs.getInt(10));
			prestmt.setBigDecimal(11, rs.getBigDecimal(11));
			prestmt.setBigDecimal(12, rs.getBigDecimal(12));
			prestmt.setBigDecimal(13, rs.getBigDecimal(13));
			prestmt.setBigDecimal(14, rs.getBigDecimal(14));
			prestmt.setString(15, rs.getString(15));
			prestmt.setInt(16, rs.getInt(16));
			prestmt.setString(17, rs.getString(17));
			prestmt.setString(18, rs.getString(18));
			prestmt.executeUpdate();
		}
		conn.close();
	}

	public void updateDuration() throws SQLException {
		ArrayList<Integer> sids = new ArrayList<Integer>();
		ArrayList<Integer> durations = new ArrayList<Integer>();
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement prestmt = null;
		try {
			conn = MysqlDbFactory.getConnection(DbConstants.DB_LYOUU);
			stmt = conn.createStatement();
			String sql = "SELECT sid,departdate,enddate FROM ly_bustour_schedule ORDER BY sid";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				sids.add(rs.getInt("sid"));
				durations.add(Util.calcDuration(rs.getDate("departdate")
						.toString(), rs.getDate("enddate").toString()));
			}
			rs.close();
			stmt.close();

//			conn.setAutoCommit(false);
//			int batchLimit = 1000;
			sql = "UPDATE ly_bustour_schedule SET duration=? WHERE sid=?";
			prestmt = conn.prepareStatement(sql);
			for (int i = 0; i < sids.size(); i++) {
				System.out.println(i + ": " + sids.get(i) + "\t"
						+ durations.get(i));
				prestmt.setInt(1, durations.get(i));
				prestmt.setInt(2, sids.get(i));
				prestmt.executeUpdate();
//				prestmt.addBatch();
//				batchLimit--;
				
//				if(batchLimit == 0){
//					prestmt.executeBatch();
//					conn.commit();
//					prestmt.clearBatch();
//					batchLimit = 1000;
//				}
			}	
		} catch (SQLException e) {
			e.printStackTrace();
			//conn.rollback();
		} finally {
			//conn.setAutoCommit(true);
			if (prestmt != null)
				prestmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static void main(String[] args) {
		try {
			// new BustourScheduleTransfer().changeDateType();
			new BustourScheduleTransfer().updateDuration();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}
